package course;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;

public class JDBCTest {
    public static void main(String[] args) throws SQLException {
        Connection connection = JavaUtil.getConnection();

        //查询
//        Query(connection);

        //登录
//        Login(connection);

        //增加
//        Add(connection);

        //删除
//        Delete(connection);

        //修改
//        update(connection);

        //注册
//        Register(connection);
    }

    @Test
    private static void Register(Connection connection) throws SQLException {
        String sqlRegister = "select * from tb_brand where brand_name =?";
        String name = "苹果";
        PreparedStatement psttm = connection.prepareStatement(sqlRegister);
        psttm.setString(1,name);
        ResultSet rs = psttm.executeQuery();
        if(rs.next()){
            System.out.println("账号已存在");
        }else {
            Add(connection);
        }
        JavaUtil.release(rs,psttm, connection);
    }

    private static void update(Connection connection) throws SQLException {
        String sqlUpdate = "Update tb_brand set brand_name = ? where id = ?";

        PreparedStatement psttm = connection.prepareStatement(sqlUpdate);
        psttm.setString(1,"米米");
        psttm.setInt(2,3);

        int i = -1;
        try{
            connection.setAutoCommit(false);
            i=psttm.executeUpdate();
            connection.commit();
        }catch (Exception e){
            connection.rollback();
        }
        if(i!=-1){
            System.out.println("修改成功");
        }else {
            System.out.println("修改失败");
        }
        JavaUtil.release2(psttm, connection);
    }

    private static void Delete(Connection connection) throws SQLException {
        String sqlDelete = "Delete from tb_brand where id =?";

        PreparedStatement psttm = connection.prepareStatement(sqlDelete);
        psttm.setInt(1,6);
        int i = -1;
        try{
            connection.setAutoCommit(false);
            i=psttm.executeUpdate();
            connection.commit();
        }catch (Exception e){
            connection.rollback();
        }
        if(i!=-1){
            System.out.println("删除成功");
        }else {
            System.out.println("失败失败");
        }
        JavaUtil.release2(psttm, connection);
    }

    private static void Add(Connection connection) throws SQLException {
        String sqlAdd = "insert into tb_brand (brand_name, company_name, ordered, description, STATUS) values " +
                "(?,?,?,?,?)";
        String brand_name = "苹果";
        String company_name = "美国苹果";
        int ordered = 10;
        String description = "乔布斯的智慧结晶";
        int status = 0;

        PreparedStatement psttm = connection.prepareStatement(sqlAdd);

        psttm.setString(1,brand_name);
        psttm.setString(2,company_name);
        psttm.setInt(3,ordered);
        psttm.setString(4,description);
        psttm.setInt(5,status);

        ResultSet rs = null;
        int i=-1;
        try{
            connection.setAutoCommit(false);
            i = psttm.executeUpdate();
            connection.commit();
        }catch (Exception e){
            System.out.println("回滚了");
            connection.rollback();
        }

        if(i!=-1){
            System.out.println("添加成功");
        }else {
            System.out.println("添加失败");
        }
        JavaUtil.release(rs,psttm, connection);
    }

    private static void Login(Connection connection) throws SQLException {
        String sqlLogin = "select * from tb_brand where brand_name=? and ordered =?";
        String name = "三只松鼠";
        int ordered = 5;

        PreparedStatement psttm = connection.prepareStatement(sqlLogin);
        psttm.setString(1,name);
        psttm.setInt(2,ordered);

        ResultSet rs = psttm.executeQuery();

        if(rs.next()){
            System.out.println("登录成功");
        }else{
            System.out.println("登录失败");
        }

        JavaUtil.release(rs,psttm, connection);
    }

    private static void Query(Connection connection) throws SQLException {
        String sqlQuery = "select * from tb_brand;";
        PreparedStatement psttm = connection.prepareStatement(sqlQuery);

        ResultSet rs = psttm.executeQuery();
        Brand brand;
        ArrayList<Brand> list = new ArrayList<>();
        while (rs.next()){
            int id = rs.getInt("id");
            String brand_name = rs.getString("brand_name");
            String company_name = rs.getString("company_name");
            int ordered = rs.getInt("ordered");
            String description = rs.getString("description");

            brand = new Brand();
            brand.setId(id);
            brand.setBrand_name(brand_name);
            brand.setCompany_name(company_name);
            brand.setOrdered(ordered);
            brand.setDescription(description);

            list.add(brand);
        }

        for (Brand brand1 : list) {
            System.out.println(brand1);
        }

        JavaUtil.release(rs,psttm, connection);
    }
}
